﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;

namespace QuanLiHocSinh
{
    public partial class NhapDiem : Form
    {
        OleDbConnection connect;
        public NhapDiem()
        {
            InitializeComponent();

            //===========================================================================================


            connect = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=CSDL.mdb");

            if (connect != null)
            {
                connect.Open();

                //===========================
                string point = "select * from lop";
                OleDbCommand comman = new OleDbCommand(point, connect);

                OleDbDataReader reader;
                reader = comman.ExecuteReader();
                
                while (reader.Read())
                {

                    string lop = "";
                    lop += reader.GetString(0);
                   
                    comboBox3.Items.Add(lop);
                }
                point = "select Mon from monhoc";
                comman = new OleDbCommand(point, connect);

                reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    comboBox2.Items.Add(reader.GetString(0));
                }

                //===========================
            }
            else
            {
                MessageBox.Show("Không kết nối được với CSDL", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }

        }

        private void button4_Click(object sender, EventArgs e)
        {
            listView1.Items.Clear();
            if ((comboBox1.Text == "") || (comboBox2.Text == "") || (comboBox3.Text == ""))
            {
                MessageBox.Show("Chưa chọn đủ thông tin để duyệt!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                return;
            }
            else
            {
                string ten_bang_mon = "";
                string get_ma_mon = "select MaMon from monhoc where Mon = '" + comboBox2.Text + "'";
                OleDbCommand call = new OleDbCommand(get_ma_mon, connect);
                OleDbDataReader rd = call.ExecuteReader();
                while (rd.Read())
                {
                    ten_bang_mon = rd.GetString(0);
                }

                int stt = 1;
                int hoc_ki = int.Parse(comboBox1.Text);
                string get = "select * from " + ten_bang_mon + " where HocKy = " + hoc_ki + " and Lop = '"+comboBox3.Text+"'";
                OleDbCommand comman = new OleDbCommand(get, connect);
                OleDbDataReader reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    listView1.Items.Add(stt.ToString());
                    listView1.Items[stt - 1].SubItems.Add(reader.GetString(1));
                    listView1.Items[stt - 1].SubItems.Add(reader.GetFloat(3).ToString());
                    listView1.Items[stt - 1].SubItems.Add(reader.GetFloat(4).ToString());
                    listView1.Items[stt - 1].SubItems.Add(reader.GetFloat(5).ToString());
                    listView1.Items[stt - 1].SubItems.Add(reader.GetFloat(5).ToString());
                    stt++;
                }

                get = "select * from hocsinh where Lop = '"+comboBox3.Text+"' and HoTen not in( select HoTen from "+ten_bang_mon+" where Lop = '"+comboBox3.Text+"')";
                comman = new OleDbCommand(get, connect);
                reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    listView1.Items.Add(stt.ToString());
                    listView1.Items[stt - 1].SubItems.Add(reader.GetString(1));
                    listView1.Items[stt - 1].SubItems.Add("");
                    listView1.Items[stt - 1].SubItems.Add("");
                    listView1.Items[stt - 1].SubItems.Add("");
                    listView1.Items[stt - 1].SubItems.Add("");
                    stt++;
                }

            }
        }

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //=================================================================
            textBox3.Text = listView1.Items[get_intdex(listView1)].SubItems[2].Text;
            textBox4.Text = listView1.Items[get_intdex(listView1)].SubItems[3].Text;
            textBox5.Text = listView1.Items[get_intdex(listView1)].SubItems[4].Text;
            textBox6.Text = listView1.Items[get_intdex(listView1)].SubItems[5].Text;
            //=================================================================
            string ms = "";
            string get_ms = "select MaHS from hocsinh where HoTen = '" + listView1.Items[get_intdex(listView1)].SubItems[1].Text + "'";
            OleDbCommand comman = new OleDbCommand(get_ms, connect);
            OleDbDataReader reader = comman.ExecuteReader();
            while (reader.Read())
            {
                ms = reader.GetString(0);
            }

            textBox1.Text = ms;
            textBox2.Text = listView1.Items[get_intdex(listView1)].SubItems[1].Text;

        }

        int get_intdex(ListView lv)
        {
            for (int i = 0; i < lv.Items.Count; i++)
            {
                if (lv.Items[i].Selected)
                {
                    return i;
                }
            }
            return 0;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if ((textBox1.Text == "") || (textBox2.Text == ""))
            {
                MessageBox.Show("Chưa chọn học sinh để nhập điểm!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                return;
            }
            if ((textBox3.Text == "") || (textBox4.Text == "") || (textBox5.Text == ""))
            {
                MessageBox.Show("Chưa nhập điểm đầy đủ!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                return;
            }
            if ((sai_diem(textBox3.Text)) || sai_diem(textBox4.Text) || sai_diem(textBox5.Text))
            {
                MessageBox.Show("Nhập điểm không hợp lệ!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                return;
            }
            float m15 = float.Parse(textBox3.Text);
            float m45 = float.Parse(textBox4.Text);
            float mhk = float.Parse(textBox5.Text);

            float tb = (m15 + m45 * (float)2 + mhk * (float)3) / (float)6;
            textBox6.Text = tb.ToString();
        }




        bool sai_diem(string diem)
        {
            
            float d = float.Parse(diem);
            if ((d < 0) || (d > 10))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if ((textBox1.Text == "") || (textBox2.Text == ""))
            {
                MessageBox.Show("Chưa chọn học sinh để nhập điểm!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                return;
            }
            if ((textBox3.Text == "") || (textBox4.Text == "") || (textBox5.Text == ""))
            {
                MessageBox.Show("Chưa nhập điểm đầy đủ!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                return;
            }
            if ((sai_diem(textBox3.Text)) || sai_diem(textBox4.Text) || sai_diem(textBox5.Text))
            {
                MessageBox.Show("Nhập điểm không hợp lệ!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                return;
            }

            //===============================================================

            //+++++++++++++
            float moc_dat = 0;
            string get_die_dat = "select DiemDatMon from rule";
            OleDbCommand comman = new OleDbCommand(get_die_dat, connect);
            OleDbDataReader get = comman.ExecuteReader();
            while (get.Read())
            {
                moc_dat = get.GetFloat(0);
            }
            //+++++++++++++
            float m15 = float.Parse(textBox3.Text);
            float m45 = float.Parse(textBox4.Text);
            float mhk = float.Parse(textBox5.Text);
            float tb = (m15 + m45 * (float)2 + mhk * (float)3) / (float)6;
            textBox6.Text = tb.ToString();
            byte dat_mon = 0;
            if (tb >= moc_dat)
            {
                dat_mon = 1;
            }

            listView1.Items[get_intdex(listView1)].SubItems[2].Text = textBox3.Text;
            listView1.Items[get_intdex(listView1)].SubItems[3].Text = textBox4.Text;
            listView1.Items[get_intdex(listView1)].SubItems[4].Text = textBox5.Text;
            listView1.Items[get_intdex(listView1)].SubItems[5].Text = textBox6.Text;

            string ten_bang_mon = "";
            string get_ma_mon = "select MaMon from monhoc where Mon = '" + comboBox2.Text + "'";
            OleDbCommand call = new OleDbCommand(get_ma_mon, connect);
            OleDbDataReader rd = call.ExecuteReader();
            while (rd.Read())
            {
                ten_bang_mon = rd.GetString(0);
            }


            
            string xoa = "delete from "+ten_bang_mon+" where HocKy = "+int.Parse(comboBox1.Text)+" and HoTen = '"+textBox2.Text+"' and Lop = '"+comboBox3.Text+"'";
            comman = new OleDbCommand(xoa, connect);
            comman.ExecuteNonQuery();

            string nhap = "insert into " + ten_bang_mon + "(HocKy, HoTen, Lop, Diem15, Diem45, DiemHK, DiemTB, DatMon) values(" + int.Parse(comboBox1.Text) + ", '" + textBox2.Text + "', '" + comboBox3.Text + "', " + float.Parse(textBox3.Text) + ", " + float.Parse(textBox4.Text) + ", " + float.Parse(textBox5.Text) + ", " + float.Parse(textBox6.Text) + ", " + dat_mon + ")";
            comman = new OleDbCommand(nhap, connect);
            comman.ExecuteNonQuery();
            MessageBox.Show("Nhập điểm thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
            //===============================================================
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }


    }
}
